package com.xbang.attendance.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.xbang.attendance.entity.order.DeliveryDetails;
import com.xbang.attendance.entity.order.DeliveryOrder;
import com.xbang.attendance.entity.order.PurchaseDetails;
import com.xbang.attendance.entity.order.PurchaseOrder;
import com.xbang.attendance.entity.order.StockOutLog;
import com.xbang.attendance.entity.product.Product;
import com.xbang.attendance.entity.stock.Inventory;
import com.xbang.attendance.entity.stock.InventoryRecord;
import com.xbang.attendance.entity.system.MaintenanceStation;
import com.xbang.attendance.entity.system.Warehouse;
import com.xbang.attendance.helper.importexcel.PurchaseImportHelper;
import com.xbang.attendance.security.support.SecurityUserHolder;
import com.xbang.attendance.service.DeliveryOrderService;
import com.xbang.attendance.service.InventoryService;
import com.xbang.attendance.service.ProductService;
import com.xbang.attendance.service.PurchaseOrderService;
import com.xbang.attendance.utils.DateUtil;
import com.xbang.attendance.utils.FieldUtil;
import com.xbang.core.dao.search.component.SearchComponent;
import com.xbang.core.dao.support.GenericDaoSupport;
import com.xbang.core.service.impl.BaseServiceImpl;
import com.xbang.core.utils.DateUtils;

import enumeration.InventoryOperation;

/**
 * @author huawei
 * @email alonelaval@gmail.com
 */
@Service("purchaseOrderService")
public class PurchaseOrderServiceImpl extends BaseServiceImpl implements PurchaseOrderService {

	@Autowired
	private GenericDaoSupport genericDaoSupport;

	@Autowired
	private InventoryService inventoryService;
	@Autowired
	private ProductService productService;
	@Autowired
	private DeliveryOrderService deliveryOrderService;

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.PurchaseOrderService#isPurchaseExist(java.
	 * lang.String)
	 */
	public Boolean isPurchaseExist(String code) throws Exception {
		return getPurchaseOrderByCode(code) != null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.PurchaseOrderService#convertDeliveryOrder(
	 * com.xbang.attendance.entity.order.PurchaseOrder )
	 */
	public DeliveryOrder convertDeliveryOrder(PurchaseOrder purchaseOrder, Set<PurchaseDetails> purchaseDetails) throws Exception {
		// maintenance station.
		MaintenanceStation maintenanceStation = purchaseOrder.getMaintenanceStation();

		DeliveryOrder deliveryOrder = null;

		if (null != maintenanceStation) {
			// get warehouse.
			Warehouse warehouse = maintenanceStation.getMainWarehouse();
			if (null != warehouse) {

				// get purchase order's detail.

				if (null != purchaseDetails && !purchaseDetails.isEmpty()) {
					// get warehouse's products.
					List<Inventory> inventorys = inventoryService.getInventoryByWarehouse(warehouse);
					if (null != inventorys && !inventorys.isEmpty()) {
						// //
						for (Iterator<PurchaseDetails> purchaseDetailIterator = purchaseDetails.iterator(); purchaseDetailIterator.hasNext();) {
							PurchaseDetails purchaseDetail = purchaseDetailIterator.next();
							// find product
							for (Inventory inventory : inventorys) {
								if (inventory.getProduct().getId().intValue() == purchaseDetail.getProduct().getId().intValue() && purchaseDetail.getBalanceNumber().compareTo(new BigDecimal(0)) != 0) {
									// 仓储里边的配件和订单里边的配件匹配程度（已匹配）
									if (inventory.getAmount().floatValue() > 0) {
										deliveryOrder = genericDelivery(purchaseOrder, maintenanceStation, deliveryOrder, warehouse, purchaseDetail, inventory);
										deliveryOrder.setManualGeneric(false);

									}
									//记录缺货
									if(inventory.getAmount().floatValue() <= 0 && purchaseDetail.getBalanceNumber().doubleValue() > 0){ // 保存缺货记录
										StockOutLog stock= new StockOutLog();
										stock.setDeliveryOrder(deliveryOrder);
										stock.setNumber(purchaseDetail.getBalanceNumber());
										stock.setProduct(purchaseDetail.getProduct());
										stock.setPurchaseOrder(purchaseOrder);
										stock.setWarehouse(warehouse);
										stock.setDate(new Date());
										save(stock);
									}
								}
							}

						}
					}
				}

			}
		}

		updatePurchaseOrderPercent(purchaseOrder);

		return deliveryOrder;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.PurchaseOrderService#getPurchaseOrderByKey
	 * (java.lang.Integer)
	 */
	@Override
	public PurchaseOrder getPurchaseOrderByKey(Integer id) throws Exception {
		Object object = genericDaoSupport.get(PurchaseOrder.class, id);
		return object == null ? null : (PurchaseOrder) object;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.PurchaseOrderService#getAllPurchaseOrders()
	 */
	@Override
	public List<PurchaseOrder> getAllPurchaseOrders() throws Exception {
		StringBuilder sbBuilder = new StringBuilder();
		sbBuilder.append("From PurchaseOrder ");
		return genericDaoSupport.getHibernateTemplate().find(sbBuilder.toString());
	}

	public void setGenericDaoSupport(GenericDaoSupport genericDaoSupport) {
		super.setGenericDaoSupport(genericDaoSupport);
	}

	@SuppressWarnings("unchecked")
	@Override
	public PurchaseDetails getDetailsByKey(Integer id) {
		StringBuilder sb = new StringBuilder("FROM PurchaseDetails detail WHERE detail.id = ?");
		List list = genericDaoSupport.getHibernateTemplate().find(sb.toString(), id);
		return list != null && !list.isEmpty() ? (PurchaseDetails) list.get(0) : null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * @seecom.xbang.attendance.service.PurchaseOrderService#updatePurchaseOrder(com
	 * .xbang.attendance.service.impl. PurchaseAnalyzeServiceImpl.Temp)
	 */
	@Override
	public void updatePurchaseOrder(PurchaseImportHelper purchaseImportHelper) throws Exception {

		PurchaseOrder order = getPurchaseOrderByCode(purchaseImportHelper.getPoCode());
		// /*已经生成了送货单*/
		// if(deliveryOrderService.getDeliveryOrderByPurchase(order) != null){
		// throw new Exception("已经生产了送货单，更新失败！");
		// }
		Set<PurchaseDetails> details = order.getPurchaseDetails();
		Map<String, PurchaseDetails> dMap = new HashMap<String, PurchaseDetails>();
		Map<String, PurchaseDetails> uMap = new HashMap<String, PurchaseDetails>();
		for (PurchaseImportHelper tt : purchaseImportHelper.getList()) {
			Boolean isExist = false;
			for (PurchaseDetails purchaseDetails : details) {
				/* 产品已经存在,明细存在 */
				if (tt.getProCode().equals(purchaseDetails.getProduct().getProductCode())) {
					isExist = true;
					if (uMap.containsKey(tt.getProCode())) {
						BigDecimal totalBigDecimal = BigDecimal.valueOf(tt.getProCount()).add(uMap.get(tt.getProCode()).getProductNumber());
						if (purchaseDetails.getDeliveryNumber().doubleValue() <= totalBigDecimal.doubleValue()) {
							BigDecimal balanceNumber = purchaseDetails.getBalanceNumber();
							purchaseDetails.setBalanceNumber(totalBigDecimal.subtract(purchaseDetails.getDeliveryNumber()));
							purchaseDetails.setProductNumber(totalBigDecimal);
							purchaseDetails.setServiceAmount(BigDecimal.valueOf(tt.getServiceAmount()));
							uMap.put(tt.getProCode(), purchaseDetails);
						}
					} else {
						BigDecimal totalBigDecimal = BigDecimal.valueOf(tt.getProCount() == null ? 0 : tt.getProCount());
						if (purchaseDetails.getDeliveryNumber().doubleValue() <= totalBigDecimal.doubleValue()) {
							BigDecimal balanceNumber = purchaseDetails.getBalanceNumber();
							purchaseDetails.setBalanceNumber(totalBigDecimal.subtract(purchaseDetails.getDeliveryNumber()));
							purchaseDetails.setProductNumber(totalBigDecimal);
							purchaseDetails.setServiceAmount(BigDecimal.valueOf(tt.getServiceAmount() == null ? 0 : tt.getServiceAmount()));
							uMap.put(tt.getProCode(), purchaseDetails);
						}
					}
				}
			}
			if (!isExist) { // 产品存在，明细不存在 ，增加
				if (dMap.containsKey(tt.getProCode())) {
					PurchaseDetails purchaseDetails = dMap.get(tt.getProCode());
					BigDecimal proBigDecimal = purchaseDetails.getBalanceNumber();
					proBigDecimal = BigDecimal.valueOf(tt.getProCount()).add(proBigDecimal);
					purchaseDetails.setBalanceNumber(proBigDecimal);
					purchaseDetails.setProductNumber(proBigDecimal);
					purchaseDetails.setServiceAmount(BigDecimal.valueOf(tt.getServiceAmount() == null ? 0 : tt.getServiceAmount()));
					dMap.put(tt.getProCode(), purchaseDetails);
				} else {
					PurchaseDetails details1 = new PurchaseDetails();
					details1.setProduct(productService.getProductByCode(tt.getProCode()));
					details1.setProductNumber(BigDecimal.valueOf(tt.getProCount()));
					details1.setPurchaseOrder(order);
					details1.setBalanceNumber(BigDecimal.valueOf(tt.getProCount()));
					details1.setServiceAmount(BigDecimal.valueOf(tt.getServiceAmount() == null ? 0 : tt.getServiceAmount()));
					dMap.put(tt.getProCode(), details1);
				}
			}
		}
		for (Entry<String, PurchaseDetails> temp2 : uMap.entrySet()) {
			PurchaseDetails d = temp2.getValue();
			d.setPurchaseOrder(order);
			update(d);
		}
		for (Entry<String, PurchaseDetails> temp2 : dMap.entrySet()) {
			PurchaseDetails d = temp2.getValue();
			d.setPurchaseOrder(order);
			save(d);
		}

		order = getPurchaseOrderByCode(purchaseImportHelper.getPoCode());
		dMap.clear();
		for (PurchaseDetails purchaseDetails : order.getPurchaseDetails()) {
			Boolean flagBoolean = false;
			for (PurchaseImportHelper tmpPurchaseImportHelper : purchaseImportHelper.getList()) {
				if (purchaseDetails.getProduct().getProductCode().equals(tmpPurchaseImportHelper.getProCode())) {
					flagBoolean = true;
				}
			}
			if (!flagBoolean) {
				if (purchaseDetails.getDeliveryNumber().doubleValue() == 0) {
					delete(purchaseDetails);
				}
			}
		}
	}

	@Override
	public DeliveryOrder convertDeliveryOrder(PurchaseOrder purchaseOrder) throws Exception {

		return convertDeliveryOrder(purchaseOrder, purchaseOrder.getPurchaseDetails());
	}

	public void updatePurchaseOrderPercent() throws Exception {
		List<PurchaseOrder> purchaseOrders = searchForList(
				"FROM PurchaseOrder purchseorder WHERE 1 = 1 AND (purchseorder.percentage IS NULL OR purchseorder.percentage < 99)", new HashMap());

		if (purchaseOrders != null && !purchaseOrders.isEmpty()) {
			for (PurchaseOrder purchaseOrder : purchaseOrders) {
				BigDecimal percentage = purchaseOrder.getPercentage();
				if (percentage != null && percentage.compareTo(new BigDecimal(100)) < 0) {
					purchaseOrder.setPercentage(new BigDecimal(50));
					update(purchaseOrder);
				} else {
					purchaseOrder.setPercentage(new BigDecimal(100));
					update(purchaseOrder);
				}
			}
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @seecom.xbang.attendance.service.PurchaseOrderService#
	 * updatePurchaseOrderPercent(com.xbang.attendance.entity.order.
	 * PurchaseOrder)
	 */
	public void updatePurchaseOrderPercent(PurchaseOrder purchaseOrder) throws Exception {
		if (purchaseOrder != null) {
			BigDecimal percentage = purchaseOrder.getPercentage();
			if (percentage != null && percentage.compareTo(new BigDecimal(100)) < 0) {
				purchaseOrder.setPercentage(new BigDecimal(50));
				update(purchaseOrder);
			} else {
				purchaseOrder.setPercentage(new BigDecimal(100));
				update(purchaseOrder);
			}
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.PurchaseOrderService#genericExcelFile(java
	 * .lang.String, java.lang.String[])
	 */
	@Override
	public String genericExcelFile(String webPath, String[] fields) throws Exception {
		String genericExcelFile = "PO-".concat(DateUtils.format(new Date(), "yyyyMMddhhmmss")).concat(".xls");
		File file = new File(webPath + "//" + genericExcelFile);
		// if(!file.exists()){
		// file.createNewFile();
		// }
		Date date = DateUtil.lowerDay(new Date(), 3);

		// System.out.println(DateUtil.formatDate(date));

		List orderList = getPurchseOrder(DateUtil.formatDate(date, DateUtil.DATE_FORMAT), null);

		if (!orderList.isEmpty()) {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("sheet1");
			sheet.setDefaultColumnWidth((short) 20);

			HSSFRow row = sheet.createRow(0);// 创建第一行

			int i = 0;
			for (FieldUtil.Filed field : FieldUtil.getPofields()) {
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(field.getName());
				i++;
			}

			int rowIndex = 1;
			for (Object object : orderList) {
				row = sheet.createRow(rowIndex++);
				Map m = (Map) object;
				i = 0;
				for (FieldUtil.Filed field : FieldUtil.getPofields()) {
					HSSFCell cell = row.createCell(i);
					cell = row.createCell(i);
					String tmpKey = field.getCode();
					Object tmpObject = m.get(tmpKey);
					if (tmpObject != null) {
						if (tmpObject.equals("orderDate")) {
							cell.setCellValue(DateUtils.format((Date) tmpObject, "yyyy-MM-dd"));
						} else {
							cell.setCellValue(tmpObject.toString());
						}
					} else {
						cell.setCellValue("");
					}
					i++;
				}
			}

			OutputStream os = new FileOutputStream(file);
			workbook.write(os);// 写入
			InputStream in = new FileInputStream(file);
			os.close();
			in.close();
		}
		return genericExcelFile;
	}

	/***
	 * 
	 * @return
	 */
	private PurchaseOrder getPurchaseOrderByCode(String code) {
		StringBuilder sb = new StringBuilder("FROM PurchaseOrder pur where pur.orderNumber = ? ");
		List list = genericDaoSupport.getHibernateTemplate().find(sb.toString(), code);

		if (list == null || list.isEmpty())
			return null;
		return (PurchaseOrder) list.get(0);

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.PurchaseOrderService#deletePurchaseOrder(java
	 * .lang.Integer)
	 */
	public List<Map<String, Object>> deletePurchaseOrder(Integer id) throws Exception {

		String hqlSentence = "FROM PurchaseOrder purchaseorder WHERE 1 = 1 ";
		SearchComponent searchComponent = new SearchComponent();
		searchComponent.addSearchElement("eq.purchaseorder.id", id);

		PurchaseOrder purchaseOrder = null;
		List lists = searchForList(hqlSentence, searchComponent);
		if (lists == null || lists.isEmpty()) {
			// error.
			return null;
		} else {
			String inventoryHQLSentence = "FROM InventoryRecord inventoryRecord WHERE inventoryRecord.deliveryOrder.id = :deliveryOrder ";
			String key = "deliveryOrder";
			Map<String, Object> parameters = new HashMap<String, Object>();
			purchaseOrder = (PurchaseOrder) lists.get(0);
			Set<DeliveryOrder> deliveryOrders = purchaseOrder.getDeliveryOrders();
			for (Iterator<DeliveryOrder> deliveryOrderIterators = deliveryOrders.iterator(); deliveryOrderIterators.hasNext();) {
				DeliveryOrder deliveryOrder = deliveryOrderIterators.next();
				parameters.clear();
				parameters.put(key, deliveryOrder.getId());

				List<InventoryRecord> inventoryRecords = searchForList(inventoryHQLSentence, parameters);
				if (inventoryRecords == null || inventoryRecords.isEmpty()) {
					// empty .

				} else {
					for (InventoryRecord inventoryRecord : inventoryRecords) {
						if (inventoryRecord.getLastInventoryOperation().equals(InventoryOperation.OUT)
								|| inventoryRecord.getLastInventoryOperation().equals(InventoryOperation.TRANSFERRED)
								|| inventoryRecord.getLastInventoryOperation().equals(InventoryOperation.TURE_OUT)) {
							if (inventoryRecord.getLastInventoryOperation().equals(InventoryOperation.OUT)) {
								// // 仓库需要增加这些产品
								Product product = inventoryRecord.getProduct();
								Warehouse warehouse = inventoryRecord.getWarehouse();
								Inventory inventory = inventoryService.getInventoryByParameters(warehouse, product);
								BigDecimal oriAmount = inventory.getAmount() == null ? new BigDecimal(0) : inventory.getAmount();
								BigDecimal addAmount = inventoryRecord.getAmount() == null ? new BigDecimal(0) : inventoryRecord.getAmount();
								inventory.setAmount(oriAmount.add(addAmount));
								inventory.setOutAmount(inventory.getOutAmount().subtract(addAmount));
								
								StockOutLog stocklog= inventoryService.getLog(inventoryRecord.getPurchaseOrder(), inventoryRecord.getProduct());
							
								if(stocklog != null){
									inventoryService.delete(stocklog);
								}
									
								
								inventoryService.update(inventory);
								inventoryService.delete(inventoryRecord);
								
								
								
							}
							// //
						}
					}
				}

				Set<PurchaseDetails> purchaseDetails = purchaseOrder.getPurchaseDetails();
				for (Iterator<PurchaseDetails> purchaseDetailIterator = purchaseDetails.iterator(); purchaseDetailIterator.hasNext();) {
					PurchaseDetails pu = purchaseDetailIterator.next();
					StockOutLog stocklog= inventoryService.getLog(purchaseOrder,pu.getProduct());
					if(stocklog != null){
						inventoryService.delete(stocklog);
					}
				}
				
				Set<DeliveryDetails> deliveryDetails = deliveryOrder.getDeliveryDetails();
				if (deliveryDetails == null && deliveryDetails.isEmpty()) {
					// empty
				} else {
					// delete.
					for (Iterator<DeliveryDetails> deliveryDetailIterator = deliveryOrder.getDeliveryDetails().iterator(); deliveryDetailIterator.hasNext();) {
						inventoryService.delete(deliveryDetailIterator.next());
					}
				}
				//
				inventoryService.delete(deliveryOrder);
			}

			Set<PurchaseDetails> purchaseDetails = purchaseOrder.getPurchaseDetails();
			for (Iterator<PurchaseDetails> purchaseDetailIterator = purchaseDetails.iterator(); purchaseDetailIterator.hasNext();) {
				PurchaseDetails pu = purchaseDetailIterator.next();
				StockOutLog stocklog= inventoryService.getLog(purchaseOrder,pu.getProduct());
				
				if(stocklog != null){
					inventoryService.delete(stocklog);
				}
				inventoryService.delete(pu);
			}

			inventoryService.delete(purchaseOrder);
		}
		// PurchaseOrder purchaseOrder = ""

		return null;
	}

	/**
	 * 生成送货单
	 * 
	 * @param purchaseOrder
	 *            订单
	 * @param maintenanceStation
	 *            维修站
	 * @param deliveryOrder
	 *            送货单
	 * @param warehouse
	 *            仓储
	 * @param purchaseDetail
	 *            订单明细
	 * @param inventory
	 *            库存
	 * @return
	 * @throws Exception
	 */
	private DeliveryOrder genericDelivery(PurchaseOrder purchaseOrder, MaintenanceStation maintenanceStation, DeliveryOrder deliveryOrder, Warehouse warehouse,
			PurchaseDetails purchaseDetail, Inventory inventory) throws Exception {
		// 仓库配件有余量
		// add delivery order details.
		if (deliveryOrder == null) {
			// add delivery head.
			deliveryOrder = new DeliveryOrder().initDelivery(purchaseOrder, maintenanceStation, warehouse);
			deliveryOrder.setPurchaseOrder(purchaseOrder);
			// 保存头
			inventoryService.save(deliveryOrder);
			// add delivery details.
		}
		DeliveryDetails deliveryDetails = new DeliveryDetails();
		deliveryDetails.setDeliveryOrder(deliveryOrder);
		deliveryDetails.setProduct(purchaseDetail.getProduct());
		deliveryDetails.setProductNumber(inventory.getAmount().compareTo(purchaseDetail.getProductNumber()) == 1 ? purchaseDetail.getProductNumber()
				: inventory.getAmount());
		
//		if(inventory.getAmount().compareTo(purchaseDetail.getProductNumber()) < 0){
//			StockOutLog stock= new StockOutLog();
//			stock.setDeliveryOrder(deliveryOrder);
//			stock.setNumber(purchaseDetail.getProductNumber().subtract(inventory.getAmount()));
//			stock.setProduct(purchaseDetail.getProduct());
//			stock.setPurchaseOrder(purchaseOrder);
//			stock.setWarehouse(warehouse);
//			stock.setDate(new Date());
//			save(stock);
//		}
			
			
			
		purchaseDetail.setBalanceNumber(purchaseDetail.getBalanceNumber().subtract(deliveryDetails.getProductNumber()));
		purchaseDetail.setDeliveryNumber(deliveryDetails.getProductNumber().add(
				purchaseDetail.getDeliveryNumber() == null ? new BigDecimal(0) : purchaseDetail.getDeliveryNumber()));
		inventory.setAmount(inventory.getAmount().subtract(deliveryDetails.getProductNumber()));
		/**新增出库记录*/
		inventory.setOutAmount(inventory.getOutAmount().add(deliveryDetails.getProductNumber()));
		
		
	
		// 保存订单明细
		inventoryService.save(purchaseDetail);
		// 保存送货单明细
		inventoryService.save(deliveryDetails);
		// 跟新库存信息
		inventoryService.save(inventory);
		// 构造出入库记录
		InventoryRecord inventoryRecord = new InventoryRecord().initialInventoryRecord(inventory, InventoryOperation.OUT, deliveryDetails.getProductNumber(),
				SecurityUserHolder.getCurrentUser().getUsername());
		inventoryRecord.setPurchaseOrder(purchaseOrder);
		/**为了补货需要*/
		inventoryRecord.setDeliveryOrder(deliveryOrder);
		inventoryRecord.setDeliveryDetails(deliveryDetails);
		//
		inventoryRecord.setAddProduct(false);
		// 保存出入库记录
		inventoryService.save(inventoryRecord);

		// tmpDeliveryDetails.add(deliveryDetails);
		return deliveryOrder;
	}

	/**
	 * 得到订单导出的列表
	 * 
	 * @return
	 */
	private List getPurchseOrder(String date, String ids) {
		StringBuilder sbString = new StringBuilder();
		sbString.append("SELECT ").append(" po.order_number AS orderNmber,").append(" po.order_date AS orderDate,").append(" ms.number       AS number,")
				.append(" ms.name         AS name,").append("   pro.product_code AS productCode,").append("  pro.product_Name AS productName,").append(
						" pro.specification AS specification,").append(" de.product_number AS productNumber,").append(" de.service_amount AS serviceAmount,")
				.append(" de.delivery_number AS deliveryNumber ").append(" FROM purchase_order po").append(
						" LEFT JOIN purchase_details de ON (po.id = de.purchase_order)").append(" LEFT JOIN product pro ON (pro.id = de.product_id)").append(
						" LEFT JOIN maintenance_station ms").append(" ON (po.maintenance_station = ms.id)").append(" LEFT JOIN region re ").append(
						" ON (ms.region = re.id)").append(" WHERE 1 = 1");
		if (date != null) {
			sbString.append(" and (po.order_date BETWEEN ? AND now()) ");
		}
		if (ids != null) {
			sbString.append(" and  po.id in (" + ids + ")");
		}
		sbString.append(" order by po.order_date desc,po.id asc,de.id asc");

		// StringBuilder sbBuilder = new
		// StringBuilder("SELECT po.id AS id,po.order_number AS orderNmber,ms.number AS number,").append(
		// "ms.name AS NAME,re.name AS rname ,po.order_date As orderDate ").append(" FROM purchase_order po ").append(
		// " LEFT JOIN maintenance_station ms ON (po.maintenance_station = ms.id)").append(" LEFT JOIN region re ON (ms.region = re.id)");
		// logger.info(sbBuilder.toString());
		// System.out.println(sbBuilder.toString());
		if (date != null)
			return genericDaoSupport.getJdbcTemplate().queryForList(sbString.toString(), date);
		if (ids != null)
			return genericDaoSupport.getJdbcTemplate().queryForList(sbString.toString());
		return genericDaoSupport.getJdbcTemplate().queryForList(sbString.toString());
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.xbang.attendance.service.PurchaseOrderService#getMaxOrder()
	 */
	@Override
	public int getMaxOrder() throws Exception {
		List list = genericDaoSupport.getJdbcTemplate().queryForList("SELECT compositor FROM purchase_order ORDER BY compositor ASC LIMIT 1,1");
		for (Object object : list) {
			return ((Map) object).get("compositor") == null ? 0 : Integer.parseInt(((Map) object).get("compositor").toString());
		}
		return 0;
		// return (list == null || list.isEmpty() ? 0
		// :Integer.parseInt(list.get(0).toString()));
	}

	@Override
	public String genericExcelFile(String webPath, String idstr, String[] ids) throws Exception {
		String genericExcelFile = "PO-".concat(DateUtils.format(new Date(), "yyyyMMddhhmmss")).concat(".xls");
		File file = new File(webPath + "//" + genericExcelFile);

		HSSFWorkbook workbook = new HSSFWorkbook();

		Map<String, Integer> sheets = new HashMap<String, Integer>();

		List orderList = getPurchseOrder(null, idstr);

		if (!orderList.isEmpty()) {

			// create sheet
			for (Object object : orderList) {
				String numberString = ((Map) object).get("orderNmber").toString();
				if (!sheets.containsKey(numberString)) {
					HSSFSheet sheet = workbook.createSheet(numberString);
					sheet.setDefaultColumnWidth((short) 20);
					HSSFRow row = sheet.createRow(0);// 创建第一行
					int i = 0;
					for (FieldUtil.Filed field : FieldUtil.getPofields()) {
						HSSFCell cell = row.createCell(i);
						cell.setCellValue(field.getName());
						i++;
					}
					sheets.put(numberString, 1);
				}

			}

			// if(sheets.containsKey(""));

			// HSSFSheet sheet = workbook.createSheet("sheet1");

			for (Object object : orderList) {
				String numberString = ((Map) object).get("orderNmber").toString();
				HSSFSheet sheet = workbook.getSheet(numberString);
				int rowIndex = sheets.get(numberString);
				HSSFRow row = sheet.createRow(rowIndex++);
				Map m = (Map) object;
				int i = 0;
				for (FieldUtil.Filed field : FieldUtil.getPofields()) {
					HSSFCell cell = row.createCell(i);
					cell = row.createCell(i);
					cell.setCellValue(m.get(field.getCode()) != null ? m.get(field.getCode()).toString() : "");
					i++;
				}
				sheets.put(numberString, rowIndex);
			}

			OutputStream os = new FileOutputStream(file);
			workbook.write(os);// 写入
			InputStream in = new FileInputStream(file);
			os.close();
			in.close();
		}
		return genericExcelFile;
	}

}
